package com.foreveredu.propms.persist.hibernate;

import java.util.List;

import org.hibernate.Query;

import com.foreveredu.propms.domain.Bill;
import com.foreveredu.propms.domain.BillOther;
import com.foreveredu.propms.domain.House;
import com.foreveredu.propms.persist.IBillDao;


public class HibernateBillDao extends HibernateAbstractDao<Bill, Integer>
		implements IBillDao {

	private static final long serialVersionUID = 1L;

	public HibernateBillDao() {
		super(Bill.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Bill findByHouseAndMonth(House house, int year, int month) {
		String hql = "from Bill b where b.house.id=? and b.year=? and b.month=?";
		List<Bill> billList = this.getHibernateTemplate().find(hql, new Object[]{house.getId(), year, month});
		Bill b = null;
		if (billList != null && billList.size()>0) {
			b = billList.get(0);
		}
		return b;
	}

	@Override
	public float findHistoryDebtOfHouse(House house) {
		Query query = this.getSession().createSQLQuery("select sum(amount) from t_bill where house_id=" + house.getId() + " and isPaid=0");
		Object sum = query.uniqueResult();
		float debt = 0;
		if (sum != null) {
			debt = Float.parseFloat(sum.toString());
		}
		return debt;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Bill findHistoryBillOfHouse(House house) {
		String hql = "from Bill b where b.house.id=? and b.year=? and b.month=? and b.isPaid=0";
		List<Bill> billList = this.getHibernateTemplate().find(hql, new Object[]{house.getId(), 0, 0});
		Bill b = null;
		if (billList != null && billList.size()>0) {
			b = billList.get(0);
		}
		return b;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Bill> findNotPaidBillsOfHouse(House house) {
		List<Bill> billList = null;
		String hql = "from Bill b where b.house.id=? and b.isPaid=0 order by b.house.houseId, b.year, b.month";
		billList = this.getHibernateTemplate().find(hql, house.getId());

		return billList;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Bill> findBillsByBillOther(BillOther billOther) {
		List<Bill> billList = null;
		String hql = "from Bill b where b.amount=? and b.isInvoiced=? and b.isPaid=? and b.month=? and b.paidAmount=? and b.year=? and b.house.id=?";
		billList = this.getHibernateTemplate().find(hql, new Object[]{billOther.getAmount(), billOther.getIsInvoiced(), billOther.getIsPaid(), billOther.getMonth(), billOther.getPaidAmount(), billOther.getYear(), billOther.getHouse_id()});
		
		return billList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Bill> findBillsOfHouse(House house) {
		List<Bill> billList = null;
		String hql = "from Bill b where b.house.id=? order by b.year desc , b.month desc";
		billList = this.getHibernateTemplate().find(hql, house.getId());

		return billList;
	}

}
